# encoding=UTF-8
import datetime
import sys
import time

import pymysql
from DBUtils.PooledDB import PooledDB
from config import *
import logging

sql_logger = logging.getLogger('sql_logger')
web_logger = logging.getLogger('web_logger')
err = 'conerr'
spool = locals()

def getdbconn():
    for dbname in dbnamelist:
        db_config = {
            "host": host,
            "port": port,
            "user": user,
            "db": basedbname,
            "passwd": passwd,
            "charset": "utf8",
        }
        web_logger.info(db_config)
        spool[dbname] = PooledDB(pymysql, mincached=25, maxcached=30, maxshared=0, maxconnections=0, **db_config)


getdbconn()


# 1. mincached，最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
# 2. maxcached，最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
# 3. maxconnections，最大的连接数，
# 4. blocking，当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错，
# 5. maxshared 当连接数达到这个数，新请求的连接会分享已经分配出去的连接


# 查询并返回全部数据方法(元组)
def mysql_sel(sql, dbname, cid):
    # uid = uuid.uuid1()
    try:
        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()
        cursor = conn.cursor()
        cursor.execute(sql)
        # 获取所有
        row = cursor.fetchall()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()

        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, row))
        return row
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 查询并返回全部数据方法(字典)
def mysql_seldic(sql, dbname, cid):
    try:

        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute(sql)
        # 获取所有
        row = cursor.fetchall()

        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, row))
        return row
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 查询并返回第一个字短数据方法
def mysql_selrowid(sql, dbname, cid):
    try:
        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()
        cursor = conn.cursor()
        rowcount = cursor.execute(sql)

        fistcol = ''
        if rowcount > 0:
            row_1 = cursor.fetchone()
            fistcol = row_1[0]
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, fistcol))
        return fistcol
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 修改数据库并返回修改条数
def mysql_updata(sql, dbname, cid):
    try:
        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()
        cursor = conn.cursor()
        # 执行sql，更新单条数据，并返回受影响行数
        effect_row = cursor.execute(sql)
        # 提交，保存新建或修改的数据
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, effect_row))
        return effect_row
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 插入一条数据库并返回插入条数
def mysql_insert(sql, dbname, cid):
    try:
        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()

        cursor = conn.cursor()
        # 插入多条，并返回受影响的函数
        effect_row = cursor.execute(sql)

        # 提交，保存新建或修改的数据
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, effect_row))
        return effect_row
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 插入一条数据库并返回自增的id
def mysql_insertid(sql, dbname, cid):
    try:
        # conn = pymysql.connect(
        #                host=host,
        #                port=port,
        #                user=user,
        #                passwd=passwd,
        #                db=db ,
        #                charset=charset
        #                )

        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()

        cursor = conn.cursor()
        # 插入多条，并返回受影响的函数
        effect_row = cursor.execute(sql)

        new_id = cursor.lastrowid
        # 提交，保存新建或修改的数据
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, new_id))
        return new_id
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 插入批量数据库并返回插入条数
def mysql_insertmany(sql, tem, dbname, cid):
    try:
        if dbname == "":
            dbname = basedbname
        conn = spool[dbname].connection()
        begindata = time.time()

        cursor = conn.cursor()
        # 插入多条，并返回受影响的函数
        effect_row = cursor.executemany(sql, tem)

        # 提交，保存新建或修改的数据
        conn.commit()
        # 关闭游标
        cursor.close()
        # 关闭连接
        conn.close()
        enddata = time.time()
        sql_logger.info('CID:%s SQL:%s Time:%s Ret:%s' % (cid, sql, enddata - begindata, effect_row))

        return effect_row
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        return err


# 提交事务
def mysql_commit(sql, dbname, cid):
    if dbname == "":
        dbname = basedbname
    conn = spool[dbname].connection()
    cursor = conn.cursor()

    try:
        cursor.execute(sql)

        cursor.close()
        conn.commit()
        return 1
    except Exception as e:
        sql_logger.error('%s - error:%s %s', sql, type(e), e)
        conn.rollback()
        return err
    finally:
        conn.close()